package dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import model.CriminalRegModel;
import util.dataUtil;

public class Criminal_RegDao {
	public static ArrayList getALL(){
		ArrayList listCriminalreg = new ArrayList();
		
			try {
				String sql = "SELECT * FROM Criminal_Reg";
				PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				while (rs.next()){
					CriminalRegModel model = new CriminalRegModel();
					model.setAddress(rs.getString("ADDRESS"));
					model.setBirth(rs.getString("BIRTH"));
					model.setFirstName(rs.getString("FIRSTNAME"));
					model.setId(rs.getInt("ID"));
					model.setIdUser(rs.getInt("IDUSER"));
					model.setImage(rs.getString("IMAGE"));
					model.setLastName(rs.getString("LASTNAME"));
					model.setSex(rs.getInt("SEX"));
					model.setLastUpdated(rs.getString("Last_updated"));
					listCriminalreg.add(model);
				}
					
			} catch (Exception e) {
				e.printStackTrace();
			}
			return listCriminalreg;
	}
	public static ArrayList getbyID(int ID){
		ArrayList listCriminalreg = new ArrayList();
		String sql = "SELECT * FROM Criminal_Reg where ID =?";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ps.setInt(1, ID);
			ResultSet rs = ps.executeQuery();
	while (rs.next()){
		CriminalRegModel model = new CriminalRegModel();
		model.setId(rs.getInt("ID"));
		model.setFirstName(rs.getString("FIRSTNAME"));
		model.setLastName(rs.getString("LASTNAME"));
		model.setBirth(rs.getString("BIRTH"));
		model.setAddress(rs.getString("ADDRESS"));
		model.setSex(rs.getInt("SEX"));
		model.setImage(rs.getString("IMAGE"));
		model.setIdUser(rs.getInt("IDUSER"));
		model.setLastUpdated(rs.getString("Last_updated"));
		listCriminalreg.add(model);
		
	}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listCriminalreg;
		
	}
	public static int updateCriminalReg(CriminalRegModel model){
		int kq =0;
		try {
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_UpdateCRIMINAL_REG(?,?,?,?,?,?,?,?,?,?)}");
			csmt.setInt("ID", model.getId());
			csmt.setString("FIRSTNAME", model.getFirstName());
			csmt.setString("LASTNAME", model.getLastName());
			csmt.setString("BIRTH", model.getBirth());
			csmt.setString("ADDRESS", model.getAddress());
			csmt.setInt("SEX", model.getSex());
			csmt.setString("IMAGE", model.getImage());
			csmt.setInt("IDUSER", model.getIdUser());
			csmt.setString("Last_updated", model.getLastUpdated());
			csmt.registerOutParameter("result", java.sql.Types.INTEGER);
			csmt.execute();
			kq = csmt.getInt("result");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	public static int insertCriminalReg(CriminalRegModel model){
		int kq =0;
		try {
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_InsertCRIMINAL_REG(?,?,?,?,?,?,?,?,?)}");
			csmt.setString("FIRSTNAME", model.getFirstName());
			csmt.setString("LASTNAME", model.getLastName());
			csmt.setString("BIRTH", model.getBirth());
			csmt.setString("ADDRESS", model.getAddress());
			csmt.setInt("SEX", model.getSex());
			csmt.setString("IMAGE", model.getImage());
			csmt.setInt("IDUSER", model.getIdUser());
			csmt.setString("Last_updated", model.getLastUpdated());
			csmt.registerOutParameter("result", java.sql.Types.INTEGER);
			csmt.execute();
			kq = csmt.getInt("result");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
	public static int deleteCriminalReg(CriminalRegModel model){
		int kq =0;
		try {
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_DeleteCRIMINAL_REG(?,?)}");
			csmt.setInt("ID", model.getId());
			csmt.registerOutParameter("result", java.sql.Types.INTEGER);
			csmt.execute();
			kq = csmt.getInt("result");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return kq;
	}
}